<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Centermod extends CI_Model 
{

    function __construct()
    {
        parent::__construct();
    	$this->load->database();

	}
	
//################## commun function################################
	public function updatetemplate($tablename,$data,$where)
		{
				
			$str = $this->db->update_string($tablename, $data, $where);
			
			$query = $this->db->query($str);
			//print_r($str);
			
		}	
		
	public function insertetemplate($tablename,$data)
		{
				
			$str = $this->db->insert_string($tablename, $data);
			 
			$query = $this->db->query($str);
			$idinsert=$this->db->insert_id();
			return $idinsert;
			
		}	

		
		
//######################### end commun function###########################

//########################## liste tour ##################################

	//list year of tour		
	public function ltouryear()
		{
				
			$query = $this->db->query("SELECT DISTINCT (year( date )) as year FROM tours order by year(date) ");

			return $query->result_array();			 
						
		}
	
	//list tour by year
	public function ltourbyyear($year)
		{
				
			$query = $this->db->query("SELECT  * FROM tours where year(date)=$year order by date");
			return $query->result_array();			 
						
		}	

		
//liste tour by id
	public function ltourbyid($id)
		{
				
			$query = $this->db->query("select * from tours where idtours=$id");
			return $query->result_array();			 
						
		}	

//liste forfait by idtours		
	public function lforfaitbyid($idtour)
		{
				
			$query = $this->db->query("SELECT * FROM forfaits where fk_idtours=$idtour");
			return $query->result_array();			 
						
		}	
		
		
 //list bus 
 	public function lbus($idtour)
		{
				
			$query = $this->db->query("SELECT *FROM  bus WHERE fk_idtours =$idtour");
			return $query->result_array();			
		}
//check bus is tour's bus

 	public function istoursbus($idbus,$idtour)
		{
				
			$query = $this->db->query("SELECT * FROM bus WHERE idbus=$idbus and fk_idtours=$idtour");
			if( count($query->result_array())==1)
				return true;
				else return false;		
		}
// search client by telephone
	public function seachclientbytel($tel)
		{
				
			$query = $this->db->query("SELECT *FROM clients WHERE telphone ='$tel'");
			return $query->result_array();			
		}
		
		
	public function seachclientbyid($id)
		{
				
			$query = $this->db->query("SELECT * FROM clients WHERE idclients =$id");
			return $query->result_array();			
		}	

		
	public function countchambre($idbus)
		{
				
			$query = $this->db->query("SELECT sum( chambreqty ) AS total, chambre FROM inscriptions WHERE fk_idbus =$idbus GROUP BY chambre");
			return $query->result_array();			
		}	

	public function linfodetail($idbus,$idtour)
		{
			$query = $this->db->query("SELECT b.code, b.guider, b.bus_company, b.driver, b.hotel_info, t.name ,t.date FROM bus AS b INNER JOIN tours AS t ON t.idtours = b.fk_idtours
WHERE idbus =$idbus AND t.idtours =$idtour");
			return $query->result_array();			
		}	

	/*list client  */
	public function lclientdetail($idbus,$idtour)
		{			
			$query = $this->db->query("SELECT i.salesprice, i.numberpeople,i.nbchild, i.chambre, i.paid, i.reminder, 
			c.firstname, c.lastname, c.telphone, c.email, c.language,i.group,i.chambreqty,ri.inv_idinvoice ,c.idclients ,i.idinscriptions 	
FROM inscriptions AS i 
INNER JOIN clients AS c ON i.fk_idclients = c.idclients 
INNER JOIN bus AS b ON b.idbus = i.fk_idbus
inner join relatif_invoice as ri on ri.insrc_idinscriptions=i.idinscriptions 
WHERE i.fk_idbus =$idbus AND b.fk_idtours =$idtour
AND i.active =1 AND i.group=0");	
			return $query->result_array();	
		}
		
public function getclientgourpid($idbus,$idtour)
		{			
			$query = $this->db->query("SELECT i.fk_idclients,i.salesprice, i.numberpeople,  i.paid, i.reminder, c.firstname, c.lastname, c.telphone, c.email, c.language,i.group 
FROM inscriptions AS i INNER JOIN clients AS c ON i.fk_idclients = c.idclients INNER JOIN bus AS b ON b.idbus = i.fk_idbus WHERE i.fk_idbus =$idbus AND b.fk_idtours =$idtour
AND i.active =1 AND i.group=1 group by i.fk_idclients");	
			return $query->result_array();	
		}			


public function lclientdetailbygourp($idbus,$idtour,$idclient)
		{			
			/*$query = $this->db->query("SELECT i.chambre,i.chambreqty 
FROM inscriptions AS i INNER JOIN clients AS c ON i.fk_idclients = c.idclients INNER JOIN bus AS b ON b.idbus = i.fk_idbus WHERE i.fk_idbus =$idbus AND b.fk_idtours =$idtour
AND i.active =1 AND i.group=1 and i.fk_idclients=$idclient");	*/
			$query = $this->db->query("SELECT i.chambre,i.chambreqty ,i.numberpeople,i.salesprice,i.idinscriptions,ri.inv_idinvoice,i.reminder,i.paid
			FROM inscriptions AS i 
			INNER JOIN clients AS c ON i.fk_idclients = c.idclients 
			INNER JOIN bus AS b ON b.idbus = i.fk_idbus  
			inner join relatif_invoice as ri on ri.insrc_idinscriptions=i.idinscriptions  
			WHERE i.fk_idbus =$idbus 
			AND b.fk_idtours =$idtour 
			AND i.active =1 AND i.group=1 and i.fk_idclients=$idclient 
			order by ri.inv_idinvoice 
			");
			
			return $query->result_array();	
		}
		
public function allinfoforinvoice($inscription)
		{			
			$query = $this->db->query("select c.firstname,c.lastname,c.telphone,c.email,c.language, i.salesprice,i.numberpeople,i.orginalprice,i.nbchild,i.chambre,i.chambreqty,i.reminder as reminder,b.code,t.description,
			t.description,iv.prixtotal,iv.prixadditonal,iv.addionalprixcommentaire,iv.balance,iv.idinvoice ,iv.typepaiement ,u.name as agent,i.paid,ag.name as agency,t.name as tourname,t.code as tourcode,i.nameonline,iv.createdtime
			,t.date as tourdate,t.fixedprice,idtours,idbus,t.time as departtime,t.lieu as departlieu,iv.partners
			from inscriptions as i inner join bus  as b on fk_idbus=idbus
			 inner join tours as t on b.fk_idtours=idtours inner join clients as c on i.fk_idclients=c.idclients
			inner join relatif_invoice  as  r on r.insrc_idinscriptions=i.idinscriptions
		    inner join invoice as iv on r.inv_idinvoice=iv.idinvoice 
			inner join users as u on u.idusers=r.users_idusers
			inner join agencies as ag on ag.idagency=u.fk_idagency
		    where i.idinscriptions=$inscription");	
			return $query->result_array();	
		}

public function infobusbyid($idbus)
		{			
			$query = $this->db->query("select * from bus where idbus=$idbus");
			return $query->result_array();	
		}	
public function sustactionper($idbus)
	{
		$query = $this->db->query("select count(*) as totalrow FROM  inscriptions as i inner join relatif_invoice as ri on ri.insrc_idinscriptions=i.idinscriptions where i.fk_idbus=$idbus");
		$countrow= $query->result_array();
		if($countrow[0]["totalrow"]>0)
		{
				$query2 = $this->db->query("SELECT  sum(numberpeople+nbchild) as totalinscr FROM  inscriptions as i inner join relatif_invoice as ri on ri.insrc_idinscriptions=i.idinscriptions where i.fk_idbus=$idbus");
				$totalinscr=$query2->result_array();
				$chiffre=$totalinscr[0]["totalinscr"];
				$query3 = $this->db->query("update bus set seatavaible=seatqty-$chiffre where idbus=$idbus");
				
		}else
		{
			$query5=$this->db->query("select * from bus where idbus=$idbus");
			$countrow5= $query5->result_array();
			if(count($countrow5)>0)
			{
				$seatqty_orignal=$countrow5[0]["seatqty"];
				$query6 = $this->db->query("update bus set seatavaible=$seatqty_orignal where idbus=$idbus");
			}
		}
	}

public function getplaceinfo($idtours)
	{
		$query = $this->db->query("select seatqty,seatavaible,idbus from bus left join tours on idtours=fk_idtours where idtours=$idtours");	
		return $query->result_array();
	}	


public function getvalidplace($idbus)
	{
		$query = $this->db->query("select seatavaible from bus where idbus=$idbus");	
		return $query->result_array();
	}	
	
public function deletetranscation($idinvoice,$idinscr)
	{
		$query = $this->db->query("delete from relatif_invoice where inv_idinvoice=$idinvoice");
		$query = $this->db->query("delete FROM invoice where idinvoice=$idinvoice");

		$query = $this->db->query("delete from inscriptions where idinscriptions=$idinscr");
	}
	
public function deletetranscationgourp($idinvoice,$idinscr)
	{
		$query = $this->db->query("delete from relatif_invoice where inv_idinvoice =$idinvoice");
		$query = $this->db->query("delete FROM invoice where idinvoice =$idinvoice");
		$query = $this->db->query("delete from inscriptions where idinscriptions in ($idinscr)");
	}
public function changeflag($idinscr)
{
	$query = $this->db->query("update inscriptions set flag=1 where idinscriptions=$idinscr");
}

public function deletenoinvoice()
	{
		$query = $this->db->query("select idinscriptions 
									 FROM inscriptions
									 where idinscriptions
									 not in (select insrc_idinscriptions from  relatif_invoice ) and flag=0");
		$arr_id=$query->result_array();
		//print_r($arr_id);
		if(count($arr_id)>0){
		foreach($arr_id as $id)
		{
			$idinscr=$id["idinscriptions"];
			$query = $this->db->query("delete from inscriptions where idinscriptions=$idinscr");
			
		}
		}
	  }
	  
public function getusers()
	{
		$query = $this->db->query("select * from users where pwd !='eerr!psd8877'");
		return $query->result_array();
	}	

	
public function getuserinfobyid($iduser)
	{
		$query = $this->db->query("select * from users where pwd !='eerr!psd8877' and idusers=$iduser");
		return $query->result_array();
	} 

public function verifier($name,$pwd,$email)
  		{
  			$query=$this->db->query("select count(*) as total from users where name='$name' and pwd='$pwd' and email='$email'");
  			$arr_user=$query->result_array();
  			return $arr_user[0]["total"]  ;
  		}
  		
public function changepaid($idinscription)
	{
		$query=$this->db->query("update inscriptions set paid=1 where idinscriptions=$idinscription");
	} 
	
//chercher reminder 	
public function getreminderbyid($idinscriptions)
	{
		$query=$this->db->query("SELECT reminder FROM inscriptions where idinscriptions=$idinscriptions");
		return $query->result_array();
	}


/****************** party for partner module   ************************/	
	
public function getpartners()
	{
		$query = $this->db->query("SELECT * FROM partners  where pwd !='eerr!psd8877'");
		return $query->result_array();
	}	

	
public function getpartnersinfobyid($idpartners)
	{
		$query = $this->db->query("select * from partners where pwd !='eerr!psd8877' and idpartners=$idpartners");
		return $query->result_array();
	} 
	

public function verifierpartner($name,$pwd,$email)
  		{
  			$query=$this->db->query("select count(*) as total from partners where name='$name' and pwd='$pwd' and email='$email'");
  			$arr_user=$query->result_array();
  			return $arr_user[0]["total"]  ;
  		}
/*********************************************************************/
  		
}


